IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetWorkflowHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetWorkflowHistory]
GO 
  
/************************************************************************************************          
* Name:  [GetWorkflowHistory]                                                                    
* Purpose: This stored procedure gets the history of completed activities, associated with execution id         
*  id          
*            
* PARAMETERS            
* Name   Description          
* ------------------------- -----------------------------          
* @ProtocolProcessId    ProtocolProcess Id associated with the submission         
* @executionId          exceution Id associated with the submission
************************************************************************************************          
*Call: [GetWorkflowHistory] 6          
************************************************************************************************/          
CREATE procedure [dbo].[GetWorkflowHistory]  
(          
 @protocolProcessId int,  
 @executionId Int = 0         
)          
as          
begin          
set nocount on     
    
if @executionId = 0    
	BEGIN  
		select    
			@executionId =Min(ExecutionId)      
		from       
			ProtocolProcess_WorkFlow_Map pwm      
		where   
			pwm.ProtocolProcessId =@protocolProcessId  
			and Rtrim(Ltrim(Type)) Like 'INIT'      
	END       
	
	--Get the workflow history            
	 Exec [dbo].[GetWorkflowExecutionDetails] @executionId 

	--Get the available submission types for the process
	Select protocolProcessId,
			   ExecutionId, 
			   (cast( ROW_NUMBER() over(order by Id) as varchar(10)) +' - '+ CASE Type WHEN 'INIT' THEN 'Initial' ELSE 'Resubmission' END) as SubmissionType  
		from ProtocolProcess_workflow_map where protocolProcessId = @protocolProcessId   

	--Get the Submitter name
	-- ProtocolProcess_Workflow_Map now stores SubmitterId, but it didn't used to.  Try to get it
	-- From the map table, otherwise get submitter from PP
	Select rtrim(ltrim(adp.LastName)) +', '+rtrim(ltrim(adp.FirstName)) as SubmitterName     
	  from ProtocolProcess_Workflow_Map pwm
	  inner join ProtocolProcess pp on pp.Id = pwm.protocolProcessId
	  inner join admperson adp on adp.FwkDomainUserId = CASE ISNULL(pwm.SubmitterId, 0) WHEN 0 THEN pp.SubmitterId ELSE pwm.SubmitterId END
	  where pwm.protocolProcessId = @protocolProcessId and ExecutionId = @executionId
END  
  
  

